package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.BankcardDao;

/**
 * 
 * @author zhai
 * @version 创建时间：2015年12月14日 上午9:34:42
 * @功能：抽奖结果页面
 */

@Repository
public class BankcardDaoImpl  extends P2PJdbcDao implements BankcardDao{
	Logger logger=Logger.getLogger(BankcardDaoImpl.class);

	@Override
	/**
	 * @author zhai
	 * BankcardDao 的实现类
	 * 查询银行卡的相关SQl语句
	 */
	public List querybankcard(JSONObject params) {

		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		
		String userid = params.optString("userid");
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		String cardid = params.optString("cardid");
		String sfzhm = params.optString("sfzhm");
		
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("USERID", userid);
		paramMap.put("CARDNAME", username);
		paramMap.put("SJHM", sjhm);
		paramMap.put("CARDID", cardid);
		paramMap.put("SFZHM", sfzhm);
		sql.append(" SELECT v.USERID,t.SJHM,v.CARDID,v.SFZHM,v.TJSJ, v.YHMC,v.CARDNAME,v.KHH,v.KHHSZD  FROM t_zhfw_bankcard v , t_zhfw_user t  where v.USERID=t.USER_NAME and t.DISABLED='1' ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND TJSJ >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND TJSJ <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(userid)){
			sql.append(" AND USERID like "+ "'%"+userid+"%'");
			paramMap.put("USERID",userid);
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND CARDNAME = :username");
			paramMap.put("username",username);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
			paramMap.put("SJHM", sjhm);
		}
		if(StringUtils.isNotEmpty(cardid)){
			sql.append(" AND CARDID like "+ "'%"+cardid+"%'");
			paramMap.put("CARDID", cardid);
		}
		if(StringUtils.isNotEmpty(sfzhm)){
			sql.append(" AND SFZHM like "+ "'%"+sfzhm+"%'");
			paramMap.put("SFZHM", sfzhm);
		}
		sql.append(" order by TJSJ desc LIMIT :START,:LIMIT ");
		logger.info("querybankcard sql is : " + sql.toString());
		logger.info("querybankcard paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	@SuppressWarnings({ "deprecation", "deprecation" })
	/**
	 * @author zhai
	 * 获取银行卡信息的数量
	 */
	@Override
	public int getbankcardCount(JSONObject params) {
		
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		
		String userid = params.optString("userid");
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		String cardid = params.optString("cardid");
		String sfzhm = params.optString("sfzhm");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("USERID", userid);
		paramMap.put("CARDNAME", username);
		paramMap.put("SJHM", sjhm);
		paramMap.put("CARDID", cardid);
		paramMap.put("SFZHM", sfzhm);
	    sql.append(" SELECT COUNT(*) ");
	    sql.append(" FROM t_zhfw_bankcard v left join t_zhfw_user t on v.USERID=t.USER_NAME and t.DISABLED='1'  where 1=1 ");
	    
	    if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND TJSJ >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND TJSJ <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(userid)){
			sql.append(" AND USERID like "+ "'%"+userid+"%'");
			paramMap.put("USERID",userid);
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND CARDNAME = :username");
			paramMap.put("username",username);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
			paramMap.put("SJHM", sjhm);
		}
		if(StringUtils.isNotEmpty(cardid)){
			sql.append(" AND CARDID like "+ "'%"+cardid+"%'");
			paramMap.put("CARDID", cardid);
		}
		if(StringUtils.isNotEmpty(sfzhm)){
			sql.append(" AND SFZHM like "+ "'%"+sfzhm+"%'");
			paramMap.put("SFZHM", sfzhm);
		}		
		logger.info("getbankcardCount sql is : " + sql.toString());
		logger.info("getbankcardCount paramMap is : " + paramMap);
	return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public int addBnakcard(JSONObject parms) {
		String uuid=this.getUUId();

		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append(" INSERT INTO d_hd_p2p.t_zhfw_bankcard (ID, UserID, CardID, CardName,SFZHM,TJSJ,YHMC,KHHSZD,KHH, OPER_USERID, OPER_DATE) VALUES (:id,:userid,:cid,:name,:sfz,now(),:yhmc,:khzd,:khh , :OPER_USERID, now()) ");
		parmsMap.put("id", uuid);
		parmsMap.put("userid", parms.optString("userid"));
		parmsMap.put("cid", parms.optString("cid"));
		parmsMap.put("name", parms.optString("name"));
		parmsMap.put("sfz", parms.optString("sfz"));
		parmsMap.put("yhmc", parms.optString("yhmc"));
		parmsMap.put("khzd", parms.optString("khzd"));
		parmsMap.put("khh", parms.optString("khh"));
		parmsMap.put("OPER_USERID", parms.optString("OPER_USERID",""));
		logger.info("addBnakcard sql is "+sql.toString());
		logger.info("addBnakcard parmsMap is "+parmsMap);

		return this.getNamedParameterJdbcTemplate().update(sql.toString(), parmsMap);
	}

	@Override
	public int querybankcardid(JSONObject params) {
		String cardid = params.optString("cid");
		Map<String, Object> paramMap = new HashMap<String, Object>();
		 String sql=" SELECT count(*) FROM v_p2p_bankcard where CARDID=:CARDID ";
			paramMap.put("CARDID", cardid);
			logger.info("querybankcardid sql is "+sql);
			logger.info("querybankcardid parmsMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap);

	}

	@Override
	public List queryHdbJrkBankcardList(List hdbJkrList,JSONObject params) {
		String hdbUserIdstr = this.getHdbJKrUserIdStr(hdbJkrList);
		if(hdbUserIdstr.startsWith(",")){
			hdbUserIdstr = hdbUserIdstr.substring(1, hdbUserIdstr.length());
		}
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		
		String userid = params.optString("userid");
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		String cardid = params.optString("cardid");
		String sfzhm = params.optString("sfzhm");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("USERID", userid);
		paramMap.put("CARDNAME", username);
		paramMap.put("SJHM", sjhm);
		paramMap.put("CARDID", cardid);
		paramMap.put("SFZHM", sfzhm);
		sql.append(" SELECT t1.*,t2.SJHM FROM d_hd_p2p.t_zhfw_bankcard t1 left join t_zhfw_user t2 on t1.USERID = t2.USER_NAME ");
		sql.append(" where t1.USERID in ("+hdbUserIdstr+")  ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND t1.TJSJ >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND t1.TJSJ <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(userid)){
			sql.append(" AND t1.USERID like "+ "'%"+userid+"%'");
			paramMap.put("USERID",userid);
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND t1.CARDNAME = :username");
			paramMap.put("username",username);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t2.SJHM like "+ "'%"+sjhm+"%'");
			paramMap.put("SJHM", sjhm);
		}
		if(StringUtils.isNotEmpty(cardid)){
			sql.append(" AND t1.CARDID like "+ "'%"+cardid+"%'");
			paramMap.put("CARDID", cardid);
		}
		if(StringUtils.isNotEmpty(sfzhm)){
			sql.append(" AND t1.SFZHM like "+ "'%"+sfzhm+"%'");
			paramMap.put("SFZHM", sfzhm);
		}
		sql.append(" order by t1.TJSJ desc LIMIT :START,:LIMIT ");
		logger.info("queryHdbJrkBankcardList sql is : "+sql);
		logger.info("queryHdbJrkBankcardList paramMap is : "+paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap,
				new ResultSetExtractor<List<Map<String, String>>>() {

					@Override
					public List<Map<String, String>> extractData(ResultSet rs)
							throws SQLException, DataAccessException {
						List<Map<String, String>> list = new ArrayList<>();
						ResultSetMetaData rmeta = rs.getMetaData();
						int numColumns = rmeta.getColumnCount();
						while (rs.next()) {
							Map<String, String> map = new HashMap<>();
							for (int i = 0; i < numColumns; i++) {
								map.put(rmeta.getColumnName(i + 1).toUpperCase(), rs.getString(i + 1));
							}
							list.add(map);
						}
						return list;
					}
				});
	}
	
	private String getHdbJKrUserIdStr(List<Map<String, String>> hdbJkrList){
		StringBuffer result = new StringBuffer();
		for(Map<String, String> mapInfo : hdbJkrList){
			String userId = MapUtils.getString(mapInfo, "USER_NAME","");
			if(StringUtils.isNotEmpty(userId)){
				result.append(",'").append(userId).append("'");
			}
		}
		logger.info("getHdbJKrUserIdStr : " + result.toString());
		return result.toString();
	}

	@Override
	public List queryHdhJkrList(JSONObject params) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" select * from t_zhfw_user where sjhm in (select DFRID from t_zhfw_zqzrlb) ");
		logger.info("queryHdhJkrList sql is "+sql);
		logger.info("queryHdhJkrList parmsMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(),
				new ResultSetExtractor<List<Map<String, String>>>() {

					@Override
					public List<Map<String, String>> extractData(ResultSet rs)
							throws SQLException, DataAccessException {
						List<Map<String, String>> list = new ArrayList<>();
						ResultSetMetaData rmeta = rs.getMetaData();
						int numColumns = rmeta.getColumnCount();
						while (rs.next()) {
							Map<String, String> map = new HashMap<>();
							for (int i = 0; i < numColumns; i++) {
								if(rmeta.getColumnName(i + 1).equalsIgnoreCase("USER_PASSWORD")){
									continue;
								}
								map.put(rmeta.getColumnName(i + 1).toUpperCase(), rs.getString(i + 1));
							}
							list.add(map);
						}
						return list;
					}
				});
	}

	@Override
	public int getHdbJrkBankcardCount(List hdbJkrList, JSONObject params) {
		String hdbUserIdstr = this.getHdbJKrUserIdStr(hdbJkrList);
		if(hdbUserIdstr.startsWith(",")){
			hdbUserIdstr = hdbUserIdstr.substring(1, hdbUserIdstr.length());
		}
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		
		String userid = params.optString("userid");
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		String cardid = params.optString("cardid");
		String sfzhm = params.optString("sfzhm");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("USERID", userid);
		paramMap.put("CARDNAME", username);
		paramMap.put("SJHM", sjhm);
		paramMap.put("CARDID", cardid);
		paramMap.put("SFZHM", sfzhm);
		sql.append(" SELECT COUNT(*) FROM d_hd_p2p.t_zhfw_bankcard t1 left join t_zhfw_user t2 on t1.USERID = t2.USER_NAME ");
		sql.append(" where t1.USERID in ("+hdbUserIdstr+")  ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND t1.TJSJ >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND t1.TJSJ <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(userid)){
			sql.append(" AND t1.USERID like "+ "'%"+userid+"%'");
			paramMap.put("USERID",userid);
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND t1.CARDNAME = :username");
			paramMap.put("username",username);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t2.SJHM like "+ "'%"+sjhm+"%'");
			paramMap.put("SJHM", sjhm);
		}
		if(StringUtils.isNotEmpty(cardid)){
			sql.append(" AND t1.CARDID like "+ "'%"+cardid+"%'");
			paramMap.put("CARDID", cardid);
		}
		if(StringUtils.isNotEmpty(sfzhm)){
			sql.append(" AND t1.SFZHM like "+ "'%"+sfzhm+"%'");
			paramMap.put("SFZHM", sfzhm);
		}
		logger.info("getHdbJrkBankcardCount sql is "+sql);
		logger.info("getHdbJrkBankcardCount parmsMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public Map getHdbJkrUserInfo(String userId, String queryType) {
		if(!"HDB_JRK".equals(queryType)){
			queryType = "HDB_JRK";
		}
		if(StringUtils.isNotEmpty(userId)){
			StringBuffer sql = new StringBuffer();
			Map<String, Object> paramMap = new HashMap<String, Object>();
			sql.append(" select t1.USER_NAME,t1.SJHM, t2.* from t_zhfw_user t1 ,t_zhfw_smrz t2 where t1.USER_NAME = t2.USER_NAME AND t1.USER_NAME = :USER_NAME ");
			paramMap.put("USER_NAME", userId);
			logger.info("getHdbJkrUserInfo sql is : " + sql.toString());
			logger.info("getHdbJkrUserInfo parmsMap is : " + paramMap);
			return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
		}
		return null;
	}

	@Override
	public Map queryBankcardInfo(JSONObject params) {
		String id = params.optString("id","");
		String cardId  = params.optString("cardId","");
		logger.info("queryBankcardInfo id : " + id);
		logger.info("queryBankcardInfo cardId : " + cardId);
		if(StringUtils.isNotEmpty(id) && StringUtils.isNotEmpty(cardId)){
			StringBuffer sql = new StringBuffer();
			Map<String, Object> paramMap = new HashMap<String, Object>();
			sql.append(" SELECT * FROM t_zhfw_bankcard t1 ");
			sql.append(" where t1.ID = :ID ");
			sql.append(" AND t1.CARDID = :CARDID ");
			paramMap.put("ID", id);
			paramMap.put("CARDID", cardId);
			logger.info("queryBankcardInfo sql is : " + sql.toString());
			logger.info("queryBankcardInfo parmsMap is : " + paramMap);
			if(this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap).size() > 0){
				return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, 
						new ResultSetExtractor<Map<String, String>>() {
							@Override
							public Map<String, String> extractData(ResultSet rs)
									throws SQLException, DataAccessException {
								Map<String, String> map = new HashMap<>();
								ResultSetMetaData rmeta = rs.getMetaData();
								int numColumns = rmeta.getColumnCount();
								if (rs.next()) {
									for (int i = 0; i < numColumns; i++) {
										map.put(rmeta.getColumnName(i + 1).toUpperCase(), rs.getString(i + 1));
									}
								}
								return map;
							}
						});
			}
		}
		return null;
	}

	@Override
	public boolean delBankcardInfo(JSONObject params) {
		String id = params.optString("id","");
		String cardId  = params.optString("cardId","");
		if(StringUtils.isNotEmpty(id) && StringUtils.isNotEmpty(cardId)){
			StringBuffer sql = new StringBuffer();
			Map<String, Object> paramMap = new HashMap<String, Object>();
			sql.append(" delete from t_zhfw_bankcard");
			sql.append(" where ID = :ID ");
			sql.append(" AND CARDID = :CARDID ");
			paramMap.put("ID", id);
			paramMap.put("CARDID", cardId);
			logger.info("delBankcardInfo sql is : " + sql.toString());
			logger.info("delBankcardInfo parmsMap is : " + paramMap);
			this.getNamedParameterJdbcTemplate().update(sql.toString(),paramMap);
			return true;
		}
		return false;
	}

	@Override
	public void saveBankcardBk(Map isExsitBankcardInfo) {
		String REF_ID = MapUtils.getString(isExsitBankcardInfo, "ID","");
		String USERID = MapUtils.getString(isExsitBankcardInfo, "USERID","");
		String CARDID = MapUtils.getString(isExsitBankcardInfo, "CARDID","");
		String CARDNAME = MapUtils.getString(isExsitBankcardInfo, "CARDNAME","");
		String SFZHM = MapUtils.getString(isExsitBankcardInfo, "SFZHM","");
		String TJSJ = MapUtils.getString(isExsitBankcardInfo, "TJSJ","");
		String YHMC = MapUtils.getString(isExsitBankcardInfo, "YHMC","");
		String KHHSZD = MapUtils.getString(isExsitBankcardInfo, "KHHSZD","");
		String KHH = MapUtils.getString(isExsitBankcardInfo, "KHH","");
		String OPER_USERID = MapUtils.getString(isExsitBankcardInfo, "OPER_USERID","");
		String OPER_DATE = MapUtils.getString(isExsitBankcardInfo, "OPER_DATE","");
		String JB_OPER_USERID = MapUtils.getString(isExsitBankcardInfo, "JB_OPER_USERID","");
		String sql = "INSERT INTO d_hd_p2p.t_zhfw_bankcard_back (ID,REF_ID,UserID,CardID,CardName,SFZHM,TJSJ,YHMC,KHHSZD,KHH,OPER_USERID,OPER_DATE,JB_DATE,JB_OPER_USERID) "
				+ " values (uuid(),:REF_ID,:USERID,:CARDID,:CARDNAME,:SFZHM,:TJSJ,:YHMC,:KHHSZD,:KHH,:OPER_USERID,:OPER_DATE,now(),:JB_OPER_USERID)"; 
		Map<String, String> paramMap = new HashMap<String, String>();
		paramMap.put("REF_ID", REF_ID);
		paramMap.put("USERID", USERID);
		paramMap.put("CARDID", CARDID);
		paramMap.put("CARDNAME", CARDNAME);
		paramMap.put("SFZHM", SFZHM);
		paramMap.put("TJSJ", TJSJ);
		paramMap.put("YHMC", YHMC);
		paramMap.put("KHHSZD", KHHSZD);
		paramMap.put("KHH", KHH);
		paramMap.put("OPER_USERID", OPER_USERID);
		paramMap.put("OPER_DATE", OPER_DATE);
		paramMap.put("JB_OPER_USERID", JB_OPER_USERID);
		logger.info("saveBankcardBk sql is : " + sql.toString());
		logger.info("saveBankcardBk parmsMap is : " + paramMap);
		this.getNamedParameterJdbcTemplate().update(sql.toString(),paramMap);
	}
	

}
